Adding Disconnection Functionality to AutoLotDAL.dll

To illustrate the process of using a data adapter to push changes in a DataTable back to the database for processing, you will now update the AutoLotDAL.dll assembly created back in Chapter 21 to include a new namespace (named AutoLotDisconnectedLayer). This namespace contains a new class, InventoryDALDisLayer, that uses a data adapter to interact with a DataTable.

A good way to begin is by copying the entire AutoLot project folder you created in Chapter 21 to a new location on your hard drive and rename this folder to AutoLot (Version Two). Now use Visual Studio 2010 to activate the File > Open Project/Solution... menu option, and then open the AutoLotDAL.sln file in your AutoLot (Version Two) folder.

Defining the Initial Class Type

Insert a new class named InventoryDALDisLayer using the Project > Add Class menu option. Next, ensure you have a public class type in your new code file. Change the name of the namespace wrapping this class to AutoLotDisconnectedLayer and import the System.Data and System.Data.SqlClient namespaces.

Unlike the connection-centric InventoryDAL type, this new class doesn’t need to provide custom open/close methods because the data adapter handles the details automatically.

Begin by adding a custom constructor that sets a private string variable representing the connection string. Also, define a private SqlDataAdapter member variable, which you configure by calling a (yet to be created) helper method called ConfigureAdapter(), which takes a SqlDataAdapter output parameter:

namespace AutoLotDisconnectedLayer
{
    public class InventoryDALDisLayer
    {
        // Field data.
        private string cnString = string.Empty;
        private SqlDataAdapter dAdapt = null;

        public InventoryDALDisLayer(string connectionString)
        {
            cnString = connectionString;
    
            // Configure the SqlDataAdapter.
            ConfigureAdapter(out dAdapt);
        }
    }
}

Configuring the Data Adapter Using the SqlCommandBuilder

When you use a data adapter to modify tables in a DataSet, the first order of business is to assign the UpdateCommand, DeleteCommand, and InsertCommand properties with valid command objects (until you do so, these properties return null references).

Configuring the command objects manually for the InsertCommand, UpdateCommand, and DeleteCommand properties can entail a significant amount of code, especially if you use parameterized queries. Recall from Chapter 21 that a parameterized query allows you to build a SQL statement using a set of parameter objects. Thus, if you were to take the long road, you could implement ConfigureAdapter() to create three new SqlCommand objects manually, each of which contains a set of SqlParameter objects. At this point, you could set each object to the UpdateCommand, DeleteCommand, and InsertCommand properties of the adapter.

Visual Studio 2010 provides several designer tools to take care of this mundane and tedious code on your behalf. These designers differ a bit based on which API you use (e.g., Windows Forms, WPF, or ASP.NET), but their overall functionality is similar. You’ll see examples of using these designers throughout this book, including some Windows Forms designers later in this chapter.

You won’t need to author the numerous code statements to configure a data adapter fully at this time; instead, you can take a massive shortcut by implementing ConfigureAdapter() like this:

private void ConfigureAdapter(out SqlDataAdapter dAdapt)
{
    // Create the adapter and set up the SelectCommand.
    dAdapt = new SqlDataAdapter("Select * From Inventory", cnString);

    // Obtain the remaining command objects dynamically at runtime
    // using the SqlCommandBuilder.
    SqlCommandBuilder builder = new SqlCommandBuilder(dAdapt);
}

To simplify the construction of data adapter objects, each of the Microsoft-supplied ADO.NET data providers provides a command builder type. The SqlCommandBuilder automatically generates the values contained within the SqlDataAdapter’s InsertCommand, UpdateCommand, and DeleteCommand properties, based on the initial SelectCommand. The benefit here is that you do not need to build all the SqlCommand and SqlParameter types by hand.

Here’s an obvious question at this point: how is a command builder able to build these SQL command objects on the fly? The short answer is metadata. When you call the Update() method of a data adapter at runtime, the related command builder will read the database’s schema data to autogenerate the underlying insert, delete, and update command objects.

Obviously, doing so requires additional roundtrips to the remote database; this means it will hurt performance if you use the SqlCommandBuilder numerous times in a single application. Here, you minimize the negative effect by calling your ConfigureAdapter() method at the time the InventoryDALDisLayer object is constructed, retaining the configured SqlDataAdapter for use throughout the object’s lifetime.

In the previous code snippet, you did not use the command builder object (SqlCommandBuilder, in this case) beyond passing in the data adapter object as a constructor parameter. As odd as this might seem, this is all you must do (at a minimum). Under the hood, this type configures the data adapter with the remaining command objects.

While you might love the idea of getting something for nothing, you should understand that command builders come with some critical restrictions. Specifically, a command builder is only able to autogenerate SQL commands for use by a data adapter if all of the following conditions are true:

The SQL Select command interacts with only a single table (e.g., no joins).
The single table has been attributed with a primary key.
The table must have a column or columns representing the primary key that you include in your SQL Select statement.

Based on the way you constructed your AutoLot database, these restrictions pose no problem. However, in a more industrial-strength database, you will need to consider whether this type is at all useful (if not, remember that Visual Studio 2010 will autogenerate a good deal of the required code using various database designer tools, as you will see later).

Implementing GetAllInventory()

Now that your data adapter is ready to go, the first method of your new class type will use the Fill() method of the SqlDataAdapter object to fetch a DataTable representing all records in the Inventory table of the AutoLot database:

public DataTable GetAllInventory()
{
    DataTable inv = new DataTable("Inventory");
    dAdapt.Fill(inv);
    return inv;
}

Implementing UpdateInventory()

The UpdateInventory() method is simple:

public void UpdateInventory(DataTable modifiedTable)
{
    dAdapt.Update(modifiedTable);
}

Here, the data adapter object examines the RowState value of each row of the incoming DataTable. Based on this value (e.g., RowState.Added, RowState.Deleted, or RowState.Modified), the correct command object is leveraged behind the scenes.

Setting Your Version Number

Great! At this point, the logic of the second version of your data access library is complete. You are not required to do so, but set the version number of this library to 2.0.0.0, just for good housekeeping. As described in Chapter 14, you can change the version of a .NET assembly by double-clicking the Properties node of your Solution Explorer, and then clicking the Assembly Information... button located in the Application tab. In the resulting dialog box, set the Major number of the Assembly Version to the value of 2 (see Chapter 14 for more details). Once you do this, recompile your application to update the assembly manifest.

Source Code You can find the AutoLotDAL (Version 2) project under the Chapter 22 subdirectory.

Testing the Disconnected Functionality

At this point, you can build a front end to test your new InventoryDALDisLayer class. Once again, you will use the Windows Forms API to display your data on a graphical user interface. Create a new Windows Forms application named InventoryDALDisconnectedGUI and change your initial Form1.cs file to MainForm.cs using the Solution Explorer. Once you create the project, set a reference to your updated AutoLotDAL.dll assembly (be sure you pick version 2.0.0.0!) and import the following namespace:

using AutoLotDisconnectedLayer;

The design of the form consists of a single Label, DataGridView (named inventoryGrid), and Button control (named btnUpdateInventory), which you configure to handle the Click event. Here is the definition of the form:

public partial class MainForm : Form
{
    InventoryDALDisLayer dal = null;

    public MainForm()
    {
        InitializeComponent();

        string cnStr =
        @"Data Source=(local)\SQLEXPRESS;Initial Catalog=AutoLot;" +
            "Integrated Security=True;Pooling=False";

        // Create our data access object.
        dal = new InventoryDALDisLayer(cnStr);

        // Fill up our grid!
        inventoryGrid.DataSource = dal.GetAllInventory();
    }

    private void btnUpdateInventory_Click(object sender, EventArgs e)
    {
        // Get modified data from the grid.
        DataTable changedDT = (DataTable)inventoryGrid.DataSource;

        try
        {
            // Commit our changes.
            dal.UpdateInventory(changedDT);
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

Once you create the InventoryDALDisLayer object, you can bind the DataTable returned from GetAllInventory() to the DataGridView object. When the user clicks the Update button, you extract the modified DataTable from the grid (with the DataSource property) and pass it into your UpdateInventory() method.

That’s it! After you run this application, add a set of new rows to the grid and update/delete a few others. Assuming you click the Button control, you will see your changes have persisted into the AutoLot database.

Source Code You can find the updated InventoryDALDisconnectedGUI project under the Chapter 22 subdirectory.